//
//  SqliteHelper.m
//  lv678
//
//  Created by apple on 15/8/5.
//  Copyright (c) 2015年 lampshade. All rights reserved.
//  数据库操作类
/**
 *1.创建数据库
 *2.创建表
 *3.将接口中的数据存入数据库中
 */
#import "SqliteHelper.h"
@interface SqliteHelper()
@property (nonatomic, copy) NSString *sql;
@property (nonatomic, copy) NSString *CityCode;
@property (nonatomic, copy) NSString *CityName;
@property (nonatomic, copy) NSString *EnglishName;
@property (nonatomic, copy) NSString *IsTop;
@property (nonatomic, copy) NSString *IsTopType;
@property (nonatomic, copy) NSString *Nation;
@property (nonatomic, copy) NSString *NationName;
@property (nonatomic, copy) NSString *PinYin;
@property (nonatomic, copy) NSString *PortCode;
@property (nonatomic, copy) NSString *PortEnglishName;
@property (nonatomic, copy) NSString *PortName;
@property (nonatomic, copy) NSString *PortShortChar;
@property (nonatomic, copy) NSString *PortShortName;
@property (nonatomic, copy) NSString *ShortChar;
@property (nonatomic, copy) NSString *UserType;
@property (nonatomic, copy) NSString *ID;
@property (nonatomic, copy) NSString *island;
@end
@implementation SqliteHelper
//@synthesize CityCode,CityName,EnglishName,IsTop,IsTopType,Nation,NationName,PinYin,PortCode,PortEnglishName,PortName,PortShortName,PortShortChar,ShortChar,UserType,ID,island;
/**
 *创建FMDatabase
 */

- (FMDatabase *)SqliteUtil{
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentDirectory = [paths objectAtIndex:0];
    NSString *dbPath = [documentDirectory stringByAppendingPathComponent:@"PlaneCode.db"];
    FMDatabase *db = [FMDatabase databaseWithPath:dbPath] ;

    return db;
}

/**
 *执行插入语句
 */

- (void)insertData:(NSString *)dict{
    FMDatabase *db = [self SqliteUtil];
    NSArray *CityArr = [NSArray array];
    CityArr = [dict valueForKey:@"DataList"];
    if ([db open]) {
        [db executeUpdate:@"CREATE TABLE IF NOT EXISTS PlaneCode(ID text, CityCode text, CityName text, NationName text, EnglishName text, IsTop text, IsTopType text, Nation text, PinYin text, PortCode text, PortEnglishName text, PortName text, PortShortChar text, PortShortName text, ShortChar text, UserType text, island text)"];
        for (int i = 0; i<CityArr.count; i++) {
            NSString *CityCode = [NSString stringWithString:[CityArr valueForKey:@"CityCode"][i]];
            NSString *CityName = [CityArr valueForKey:@"CityName"][i];
            NSString *NationName = [CityArr valueForKey:@"NationName"][i];
            NSString *EnglishName = [CityArr valueForKey:@"EnglishName"][i];
            NSString *IsTop = [CityArr valueForKey:@"IsTop"][i];
            NSString *IsTopType = [CityArr valueForKey:@"IsTopType"][i];
            NSString *Nation = [CityArr valueForKey:@"Nation"][i];
            NSString *PinYin = [CityArr valueForKey:@"PinYin"][i];
            NSString *PortCode = [CityArr valueForKey:@"PortCode"][i];
            NSString *PortEnglishName = [CityArr valueForKey:@"PortEnglishName"][i];
            NSString *PortName = [CityArr valueForKey:@"PortName"][i];
            NSString *PortShortChar = [CityArr valueForKey:@"PortShortChar"][i];
            NSString *PortShortName = [CityArr valueForKey:@"PortShortName"][i];
            NSString *ShortChar = [CityArr valueForKey:@"ShortChar"][i];
            NSString *UserType = [CityArr valueForKey:@"UserType"][i];
            NSString *ID = [CityArr valueForKey:@"id"][i];
            NSString *island = [CityArr valueForKey:@"island"][i];
            
            [db executeUpdate:@"INSERT INTO PlaneCode (CityCode,CityName,NationName,EnglishName,IsTop,IsTopType,Nation,PinYin,PortCode,PortEnglishName,PortName,PortShortChar,PortShortName,ShortChar,UserType,ID,island) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",CityCode,CityName,NationName,EnglishName,IsTop,IsTopType,Nation,PinYin,PortCode,PortEnglishName,PortName,PortShortChar,PortShortName,ShortChar,UserType,ID,island];
        }
        FMResultSet *result = [db executeQuery:@"select PortShortChar form PlaneConde"];
        while (result.next) {
            NSLog(@"%@",[result stringForColumn:@"PortShortChar"]);
        }
    }else{
        return;
    }
}

/**
 *通过点击查询按钮，将城市对应的三字码返回到接口中去
 *因SHB对应不同的城市，故把上海替换成上海虹桥
 *
 */

- (NSString *)queryData:(NSString *)dict{
    FMDatabase *db = [self SqliteUtil];
    NSString *Code;
    if ([db open]) {
        if ([dict isEqualToString:@"上海"]) {
            dict = @"上海虹桥";
        }
        FMResultSet *result = [db executeQuery:@"SELECT *FROM PlaneCode WHERE PortShortName=?",dict];
        while ([result next]) {
            Code = [result stringForColumn:@"PortCode"];
        }
    }
    return Code;
}

- (NSString *)queryCityName:(NSString *)dict{
    FMDatabase *db = [self SqliteUtil];
    NSString *airPort_Name;
    if ([db open]) {
        FMResultSet *result = [db executeQuery:@"SELECT * FROM PlaneCode WHERE PORTCode = ?",dict];
        while ([result next]) {
            airPort_Name = [result stringForColumn:@"PortName"];
        }
    }
    return airPort_Name;
}
@end
